/*
 * Data Manager API
 * 
 */

if (typeof iForumDAO == "undefined") {
	var iForumDAO = {
		_log : new Logger("iForumDAO"),

		conn : null,
		stmt_folder_insert : null,
		stmt_folder_singleselect : null,
		stmt_folder_select : null,
		stmt_folder_update : null,
		stmt_folder_delete : null,
		default_folder_id : 1,

		stmt_bookmark_insert : null,
		stmt_bookmark_singleselect : null,
		stmt_bookmark_select : null,
		stmt_bookmark_update : null,
		stmt_bookmark_delete : null,

		stmt_topic_insert : null,
		stmt_topic_singleselect : null,
		stmt_topic_select : null,
		stmt_topic_select_all : null,
		stmt_topic_update : null,
		stmt_topic_delete : null,

		stmt_thread_insert : null,
		stmt_thread_singleselect : null,
		stmt_thread_select_author : null,
		stmt_thread_select : null,
		stmt_thread_update : null,
		stmt_thread_delete : null,
		stmt_thread_delete_topic : null,

		stmts : null,
		ready : false,
		error : null
	};

	// manipulate folders.
	iForumDAO.getFolders = function() {
		var folders = new Array();
		var stmt = iForumDAO.stmt_folder_select;
		try {
			while (stmt.executeStep()) {
				var folder = {
					id : stmt.getInt32(0),
					name : stmt.getUTF8String(1)
				}
				folders.push(folder);
			}
		} finally {
			stmt.reset();
		}
		return folders;
	};

	iForumDAO.getFolder = function(id) {
		var folder = null;
		var stmt = iForumDAO.stmt_folder_singleselect;
		try {
			stmt.bindInt32Parameter(0, id);
			while (stmt.executeStep()) {
				folder = {
					id : stmt.getInt32(0),
					name : stmt.getUTF8String(1)
				}
				break;
			}
		} finally {
			stmt.reset();
		}
		return folder;
	};

	iForumDAO.createFolder = function(name) {
		this.stmt_folder_insert.bindUTF8StringParameter(0, name);
		this.stmt_folder_insert.execute();
		var folder = {
			id : this.conn.lastInsertRowID,
			name : name
		};
		return folder;
	};

	iForumDAO.updateFolder = function(folder) {
		// can't update default folder.
		if (folder.id == this.default_folder_id)
			return;
		this.stmt_folder_update.bindInt32Parameter(0, folder.id);
		this.stmt_folder_update.bindUTF8StringParameter(1, folder.name);
		this.stmt_folder_update.execute();
	};

	iForumDAO.deleteFolder = function(id) {
		// can't delete default folder.
		this.stmt_folder_delete.bindInt32Parameter(0, id);
		this.stmt_folder_delete.execute();
	};

	// manipulate bookmarks.
	iForumDAO.getBookmarks = function(folderid) {
		var bookmarks = new Array();
		var stmt = iForumDAO.stmt_bookmark_select;
		try {
			stmt.bindInt32Parameter(0, folderid);
			while (stmt.executeStep()) {
				var bookmark = {
					id : stmt.getInt32(0),
					topicid : stmt.getInt32(1),
					threadid : stmt.getInt32(2),
					condition : stmt.getUTF8String(3),
					createtime : stmt.getUTF8String(4),
					folderid : stmt.getInt32(5)
				}
				bookmarks.push(bookmark);
			}
		} finally {
			stmt.reset();
		}
		return bookmarks;
	};

	iForumDAO.getAllBookmarks = function(limit) {
		var bookmarks = new Array();
		var sql = "SELECT * FROM 'bookmark' ORDER BY createtime DESC";
		if (limit)
			sql + " LIMIT " + limit
		var stmt = iForumDAO.conn.createStatement(sql);
		try {
			while (stmt.executeStep()) {
				var bookmark = {
					id : stmt.getInt32(0),
					topicid : stmt.getInt32(1),
					threadid : stmt.getInt32(2),
					condition : stmt.getUTF8String(3),
					createtime : stmt.getUTF8String(4),
					folderid : stmt.getInt32(5)
				}
				bookmarks.push(bookmark);
			}
		} finally {
			stmt.reset();
			stmt.finalize();
		}
		return bookmarks;
	};

	iForumDAO.getBookmark = function(id) {
		var bookmark = null;
		var stmt = iForumDAO.stmt_bookmark_singleselect;
		try {
			stmt.bindInt32Parameter(0, id);
			while (stmt.executeStep()) {
				bookmark = {
					id : stmt.getInt32(0),
					topicid : stmt.getInt32(1),
					threadid : stmt.getInt32(2),
					condition : stmt.getUTF8String(3),
					createtime : stmt.getUTF8String(4),
					folderid : stmt.getInt32(5)
				}
				break;
			}
		} finally {
			stmt.reset();
		}
		return bookmark;
	};

	iForumDAO.createBookmark = function(topicid, threadid, condition,
			createtime, folderid) {
		this.stmt_bookmark_insert.bindInt32Parameter(0, topicid);
		this.stmt_bookmark_insert.bindInt32Parameter(1, threadid);
		this.stmt_bookmark_insert.bindUTF8StringParameter(2, condition);
		this.stmt_bookmark_insert.bindUTF8StringParameter(3, createtime);
		this.stmt_bookmark_insert.bindInt32Parameter(4, folderid);
		this.stmt_bookmark_insert.execute();
		var bookmark = {
			id : this.conn.lastInsertRowID,
			topicid : topicid,
			threadid : threadid,
			condition : condition,
			createtime : createtime,
			folderid : folderid
		};
		return bookmark;
	};

	iForumDAO.updateBookmark = function(bookmark) {
		this.stmt_bookmark_update.bindInt32Parameter(0, bookmark.id);
		this.stmt_bookmark_update.bindInt32Parameter(1, bookmark.topicid);
		this.stmt_bookmark_update.bindInt32Parameter(2, bookmark.threadid);
		this.stmt_bookmark_update
				.bindUTF8StringParameter(3, bookmark.condition);
		this.stmt_bookmark_update.bindUTF8StringParameter(4,
				bookmark.createtime);
		this.stmt_bookmark_update.bindInt32Parameter(5, bookmark.folderid);
		this.stmt_bookmark_update.execute();
	};

	iForumDAO.deleteBookmark = function(id) {
		this.stmt_bookmark_delete.bindInt32Parameter(0, id);
		this.stmt_bookmark_delete.execute();
	};

	// manipulate topics.
	iForumDAO.getTopics = function(folderid) {
		var topics = new Array();
		var stmt = iForumDAO.stmt_topic_select;
		try {
			stmt.bindInt32Parameter(0, folderid);
			while (stmt.executeStep()) {
				var topic = {
					id : stmt.getInt32(0),
					title : stmt.getUTF8String(1),
					author : stmt.getUTF8String(2),
					createtime : stmt.getUTF8String(3),
					url : stmt.getUTF8String(4),
					folderid : stmt.getInt32(5),
					mark : stmt.getInt32(6),
					reply : stmt.getInt32(7),
					bookmarkid : stmt.getInt32(8),
					mainthreadid : stmt.getInt32(9)
				}
				topics.push(topic);
			}
		} finally {
			stmt.reset();
		}
		return topics;
	};

	iForumDAO.getAllTopics = function() {
		var topics = new Array();
		var stmt = iForumDAO.stmt_topic_select_all;
		try {
			while (stmt.executeStep()) {
				var topic = {
					id : stmt.getInt32(0),
					title : stmt.getUTF8String(1),
					author : stmt.getUTF8String(2),
					createtime : stmt.getUTF8String(3),
					url : stmt.getUTF8String(4),
					folderid : stmt.getInt32(5),
					mark : stmt.getInt32(6),
					reply : stmt.getInt32(7),
					bookmarkid : stmt.getInt32(8),
					mainthreadid : stmt.getInt32(9)
				}
				topics.push(topic);
			}
		} finally {
			stmt.reset();
		}
		return topics;
	};

	iForumDAO.getTopic = function(id) {
		var topic = null;
		var stmt = iForumDAO.stmt_topic_singleselect;
		try {
			stmt.bindInt32Parameter(0, id);
			while (stmt.executeStep()) {
				topic = {
					id : stmt.getInt32(0),
					title : stmt.getUTF8String(1),
					author : stmt.getUTF8String(2),
					createtime : stmt.getUTF8String(3),
					url : stmt.getUTF8String(4),
					folderid : stmt.getInt32(5),
					mark : stmt.getInt32(6),
					reply : stmt.getInt32(7),
					bookmarkid : stmt.getInt32(8),
					mainthreadid : stmt.getInt32(9)
				}
				break;
			}
		} finally {
			stmt.reset();
		}
		return topic;
	};

	iForumDAO.createTopic = function(title, author, createtime, url, folderid,
			mark, reply, bookmarkid, mainthreadid) {
		this.stmt_topic_insert.bindUTF8StringParameter(0, title);
		this.stmt_topic_insert.bindUTF8StringParameter(1, author);
		this.stmt_topic_insert.bindUTF8StringParameter(2, createtime);
		this.stmt_topic_insert.bindUTF8StringParameter(3, url);
		this.stmt_topic_insert.bindInt32Parameter(4, folderid);
		this.stmt_topic_insert.bindInt32Parameter(5, mark);
		this.stmt_topic_insert.bindInt32Parameter(6, reply);
		this.stmt_topic_insert.bindInt32Parameter(7, bookmarkid);
		this.stmt_topic_insert.bindInt32Parameter(8, mainthreadid);
		this.stmt_topic_insert.execute();
		var topic = {
			id : this.conn.lastInsertRowID,
			title : title,
			author : author,
			createtime : createtime,
			url : url,
			folderid : folderid,
			mark : mark,
			reply : reply,
			bookmarkid : bookmarkid,
			mainthreadid : mainthreadid
		};
		return topic;
	};

	iForumDAO.updateTopic = function(topic) {
		this.stmt_topic_update.bindInt32Parameter(0, topic.id);
		this.stmt_topic_update.bindUTF8StringParameter(1, topic.title);
		this.stmt_topic_update.bindUTF8StringParameter(2, topic.author);
		this.stmt_topic_update.bindUTF8StringParameter(3, topic.createtime);
		this.stmt_topic_update.bindUTF8StringParameter(4, topic.url);
		this.stmt_topic_update.bindInt32Parameter(5, topic.folderid);
		this.stmt_topic_update.bindInt32Parameter(6, topic.mark);
		this.stmt_topic_update.bindInt32Parameter(7, topic.reply);
		this.stmt_topic_update.bindInt32Parameter(8, topic.bookmarkid);
		this.stmt_topic_update.bindInt32Parameter(9, topic.mainthreadid);
		this.stmt_topic_update.execute();
	};

	iForumDAO.deleteTopic = function(id) {
		this.stmt_topic_delete.bindInt32Parameter(0, id);
		this.stmt_topic_delete.execute();
	};

	// manipulate threads.
	iForumDAO.getThreads = function(topicid, offset) {
		var threads = new Array();
		var stmt = iForumDAO.stmt_thread_select;
		try {
			if (!offset)
				offset = 0;
			stmt.bindInt32Parameter(0, topicid);
			stmt.bindInt32Parameter(1, offset);
			while (stmt.executeStep()) {
				var thread = {
					id : stmt.getInt32(0),
					seqno : stmt.getInt32(1),
					title : stmt.getUTF8String(2),
					author : stmt.getUTF8String(3),
					createtime : stmt.getUTF8String(4),
					content : stmt.getUTF8String(5),
					bookmarkflag : stmt.getInt32(6),
					topicid : stmt.getInt32(7)
				}
				threads.push(thread);
			}
		} finally {
			stmt.reset();
		}
		return threads;
	};

	iForumDAO.countThreadsByAuthor = function(topicid, author) {
		var count = 0;
		var sql = "SELECT COUNT(*) FROM 'thread' WHERE topicid = ?1 AND author = ?2";
		var stmt = iForumDAO.conn.createStatement(sql);
		try {
			stmt.bindInt32Parameter(0, topicid);
			stmt.bindUTF8StringParameter(1, author);
			while (stmt.executeStep()) {
				count = stmt.getInt32(0);
				break;
			}
		} finally {
			stmt.reset();
			stmt.finalize();
		}
		return count;
	};

	iForumDAO.getThreadsByAuthor = function(topicid, offset, author) {
		var threads = new Array();
		var stmt = iForumDAO.stmt_thread_select_author;
		try {
			if (!offset)
				offset = 0;
			stmt.bindInt32Parameter(0, topicid);
			stmt.bindInt32Parameter(1, offset);
			stmt.bindUTF8StringParameter(2, author);
			while (stmt.executeStep()) {
				var thread = {
					id : stmt.getInt32(0),
					seqno : stmt.getInt32(1),
					title : stmt.getUTF8String(2),
					author : stmt.getUTF8String(3),
					createtime : stmt.getUTF8String(4),
					content : stmt.getUTF8String(5),
					bookmarkflag : stmt.getInt32(6),
					topicid : stmt.getInt32(7)
				}
				threads.push(thread);
			}
		} finally {
			stmt.reset();
		}
		return threads;
	};
	iForumDAO.getThread = function(id) {
		var thread = null;
		var stmt = iForumDAO.stmt_thread_singleselect;
		try {
			stmt.bindInt32Parameter(0, id);
			while (stmt.executeStep()) {
				thread = {
					id : stmt.getInt32(0),
					seqno : stmt.getInt32(1),
					title : stmt.getUTF8String(2),
					author : stmt.getUTF8String(3),
					createtime : stmt.getUTF8String(4),
					content : stmt.getUTF8String(5),
					bookmarkflag : stmt.getInt32(6),
					topicid : stmt.getInt32(7)
				}
				break;
			}
		} finally {
			stmt.reset();
		}
		return thread;
	};

	iForumDAO.createThread = function(seqno, title, author, createtime,
			content, bookmarkflag, topicid) {
		this.stmt_thread_insert.bindInt32Parameter(0, seqno);
		this.stmt_thread_insert.bindUTF8StringParameter(1, title);
		this.stmt_thread_insert.bindUTF8StringParameter(2, author);
		this.stmt_thread_insert.bindUTF8StringParameter(3, createtime);
		this.stmt_thread_insert.bindUTF8StringParameter(4, content);
		this.stmt_thread_insert.bindInt32Parameter(5, bookmarkflag);
		this.stmt_thread_insert.bindInt32Parameter(6, topicid);
		this.stmt_thread_insert.execute();
		var thread = {
			id : this.conn.lastInsertRowID,
			seqno : seqno,
			title : title,
			author : author,
			createtime : createtime,
			content : content,
			bookmarkflag : bookmarkflag,
			topicid : topicid
		};
		return thread;
	};

	iForumDAO.updateThread = function(thread) {
		this.stmt_thread_update.bindInt32Parameter(0, thread.id);
		this.stmt_thread_update.bindInt32Parameter(1, thread.seqno);
		this.stmt_thread_update.bindUTF8StringParameter(2, thread.title);
		this.stmt_thread_update.bindUTF8StringParameter(3, thread.author);
		this.stmt_thread_update.bindUTF8StringParameter(4, thread.createtime);
		this.stmt_thread_update.bindUTF8StringParameter(5, thread.content);
		this.stmt_thread_update.bindInt32Parameter(6, thread.bookmarkflag);
		this.stmt_thread_update.bindInt32Parameter(7, thread.topicid);
		this.stmt_thread_update.execute();
	};

	iForumDAO.deleteThread = function(id) {
		this.stmt_thread_delete.bindInt32Parameter(0, id);
		this.stmt_thread_delete.execute();
	};

	iForumDAO.deleteThreads = function(topicid) {
		this.stmt_thread_delete_topic.bindInt32Parameter(0, topicid);
		this.stmt_thread_delete_topic.execute();
	};

	// get DB connection.
	iForumDAO.getDBConn = function() {
		var file = iForumExtension.getDataDir();
		file.append("iforum.sqlite");

		var storageService = Components.classes["@mozilla.org/storage/service;1"]
				.getService(Components.interfaces.mozIStorageService);
		var conn = storageService.openDatabase(file);
		return conn;
	};

	// close this dao
	iForumDAO.close = function() {
		try {
			iForumDAO.stmt_folder_insert.finalize();
			iForumDAO.stmt_folder_singleselect.finalize();
			iForumDAO.stmt_folder_select.finalize();
			iForumDAO.stmt_folder_update.finalize();
			iForumDAO.stmt_folder_delete.finalize();

			iForumDAO.stmt_bookmark_insert.finalize();
			iForumDAO.stmt_bookmark_singleselect.finalize();
			iForumDAO.stmt_bookmark_select.finalize();
			iForumDAO.stmt_bookmark_update.finalize();
			iForumDAO.stmt_bookmark_delete.finalize();

			iForumDAO.stmt_topic_insert.finalize();
			iForumDAO.stmt_topic_singleselect.finalize();
			iForumDAO.stmt_topic_select.finalize();
			iForumDAO.stmt_topic_select_all.finalize();
			iForumDAO.stmt_topic_update.finalize();
			iForumDAO.stmt_topic_delete.finalize();

			iForumDAO.stmt_thread_insert.finalize();
			iForumDAO.stmt_thread_select_author.finalize();
			iForumDAO.stmt_thread_singleselect.finalize();
			iForumDAO.stmt_thread_select.finalize();
			iForumDAO.stmt_thread_update.finalize();
			iForumDAO.stmt_thread_delete.finalize();
			iForumDAO.stmt_thread_delete_topic.finalize();

			iForumDAO.conn.close();
		} catch (e) {
			iForumDAO._log.error("close conn error: " + e);
		}
	};

	(function() {
		try {
			var conn = iForumDAO.getDBConn();
			iForumDAO.conn = conn;

			var sql = "SELECT * FROM 'folder'";
			iForumDAO.stmt_folder_select = conn.createStatement(sql);
			sql = "SELECT * FROM 'folder' WHERE id = ?1";
			iForumDAO.stmt_folder_singleselect = conn.createStatement(sql);
			sql = "INSERT INTO 'folder' (name) VALUES (?1)";
			iForumDAO.stmt_folder_insert = conn.createStatement(sql);
			sql = "UPDATE 'folder' SET name=?2 WHERE id = ?1";
			iForumDAO.stmt_folder_update = conn.createStatement(sql);
			sql = "DELETE from 'folder' WHERE id = ?1";
			iForumDAO.stmt_folder_delete = conn.createStatement(sql);

			sql = "SELECT * FROM 'bookmark' WHERE folderid = ?1";
			iForumDAO.stmt_bookmark_select = conn.createStatement(sql);
			sql = "SELECT * FROM 'bookmark' WHERE id = ?1";
			iForumDAO.stmt_bookmark_singleselect = conn.createStatement(sql);
			sql = "INSERT INTO 'bookmark' (topicid, threadid, condition, createtime, folderid) VALUES (?1, ?2, ?3, ?4, ?5)";
			iForumDAO.stmt_bookmark_insert = conn.createStatement(sql);
			sql = "UPDATE 'bookmark' SET topicid=?2, threadid=?3, condition=?4, createtime=?5, folderid=?6 WHERE id = ?1";
			iForumDAO.stmt_bookmark_update = conn.createStatement(sql);
			sql = "DELETE from 'bookmark' WHERE id = ?1";
			iForumDAO.stmt_bookmark_delete = conn.createStatement(sql);

			sql = "SELECT * FROM 'topic' WHERE folderid = ?1 ORDER BY createtime DESC";
			iForumDAO.stmt_topic_select = conn.createStatement(sql);
			sql = "SELECT * FROM 'topic' WHERE id = ?1";
			iForumDAO.stmt_topic_singleselect = conn.createStatement(sql);
			sql = "SELECT * FROM 'topic'";
			iForumDAO.stmt_topic_select_all = conn.createStatement(sql);
			sql = "INSERT INTO 'topic' (title, author, createtime, url, folderid, mark, reply, bookmarkid, mainthreadid) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)";
			iForumDAO.stmt_topic_insert = conn.createStatement(sql);
			sql = "UPDATE 'topic' SET title=?2, author=?3, createtime=?4, url=?5, folderid=?6, mark=?7, reply=?8, bookmarkid=?9, mainthreadid=?10 WHERE id = ?1";
			iForumDAO.stmt_topic_update = conn.createStatement(sql);
			sql = "DELETE from 'topic' WHERE id = ?1";
			iForumDAO.stmt_topic_delete = conn.createStatement(sql);

			var prefs = new iforum_prefs();
			var threadcount = prefs.getIntPref(prefs.PREF_PAGE_THREADCOUNT);
			iForumDAO._log.debug("threadcount: " + threadcount);

			sql = "SELECT * FROM 'thread' WHERE topicid = ?1 LIMIT "
					+ threadcount + " OFFSET ?2";
			iForumDAO.stmt_thread_select = conn.createStatement(sql);
			sql = "SELECT * FROM 'thread' WHERE topicid = ?1 AND author = ?3 LIMIT "
					+ threadcount + " OFFSET ?2";
			iForumDAO.stmt_thread_select_author = conn.createStatement(sql);
			sql = "SELECT * FROM 'thread' WHERE id = ?1";
			iForumDAO.stmt_thread_singleselect = conn.createStatement(sql);
			sql = "INSERT INTO 'thread' (seqno, title, author, createtime, content, bookmarkflag, topicid) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)";
			iForumDAO.stmt_thread_insert = conn.createStatement(sql);
			sql = "UPDATE 'thread' SET seqno=?2, title=?3, author=?4, createtime=?5, content=?6, bookmarkflag=?7, topicid=?8 WHERE id = ?1";
			iForumDAO.stmt_thread_update = conn.createStatement(sql);
			sql = "DELETE from 'thread' WHERE id = ?1";
			iForumDAO.stmt_thread_delete = conn.createStatement(sql);
			sql = "DELETE from 'thread' WHERE topicid = ?1";
			iForumDAO.stmt_thread_delete_topic = conn.createStatement(sql);

			iForumDAO.ready = true;
		} catch (e) {
			iForumDAO._log.error("initiate dao: " + e);
		}
	})();
}
